
package model;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;
import org.mybeans.factory.Transaction;

import databeans.Payment;


public class PaymentDAOSQL {
    
    private List<Connection> connectionPool = new ArrayList<Connection>();

    private String jdbcDriver;
    private String jdbcURL;
    private String jdbcUser;
    private String jdbcPass;
    
    public PaymentDAOSQL(String jdbcDriver, String jdbcURL, String jdbcUser, String jdbcPass) {
        this.jdbcDriver = jdbcDriver;
        this.jdbcURL    = jdbcURL;
        this.jdbcUser    = jdbcUser;
        this.jdbcPass    = jdbcPass;
    }
    
    private synchronized Connection getConnection() throws DAOException {
        if (connectionPool.size() > 0) {
            return connectionPool.remove(connectionPool.size()-1);
        }
        
        try {
            Class.forName(jdbcDriver);
        } catch (ClassNotFoundException e) {
            throw new DAOException(e);
        }

        try {
            return DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPass);
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }
    
    private synchronized void releaseConnection(Connection con) {
        connectionPool.add(con);
    }
    
    public void approveAllPendingPayments() throws DAOException {
    	
        Connection con = null;

        try {
        	con = getConnection();
        	
        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM `cfa_check_transaction` WHERE executed=false ORDER BY submitDate DESC");
        	//pstmt.setString(1,null);
        	ResultSet rs = pstmt.executeQuery();
        	 
        	Payment tempP;
            while (rs.next()) {
            	tempP = new Payment(rs.getInt("paymentID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("submitDate"),rs.getString("transactionType"),rs.getLong("amount"),rs.getBoolean("executed"));
            	if (tempP.getTransactionType().equals(databeans.Transaction.CHECK_DEPOSIT)) {
            		//deposit case
            		PreparedStatement pstmt1 = con.prepareStatement("UPDATE `cfa_check_transaction` set executed=true where paymentID=?");
            		pstmt1.setInt(1, tempP.getPaymentID());
	        	    pstmt1.executeUpdate();
	        	  //notice the plus
	        	    PreparedStatement pstmt2 = con.prepareStatement("UPDATE `cfa_customer` set cash=cash+? where customerID=?");
            		pstmt2.setLong(1, tempP.getAmount());
            		pstmt2.setInt(2, tempP.getCustomerID());
	        	    pstmt2.executeUpdate();
	        	    
	        	    pstmt1.close();
	        	    pstmt2.close();
	        	    
            	} else {
            		//withdrawal case
            		PreparedStatement pstmt1 = con.prepareStatement("UPDATE `cfa_check_transaction` set executed=true where paymentID=?");
            		pstmt1.setInt(1, tempP.getPaymentID());
	        	    pstmt1.executeUpdate();
	        	    //notice the minus
	        	    PreparedStatement pstmt2 = con.prepareStatement("UPDATE `cfa_customer` set cash=cash-? where customerID=?");
            		pstmt2.setLong(1, tempP.getAmount());
            		pstmt2.setInt(2, tempP.getCustomerID());
	        	    pstmt2.executeUpdate();
	        	    
	        	    pstmt1.close();
	        	    pstmt2.close();
            	}
            }
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DAOException(e);
        }
    }
}